SET NOCOUNT ON;
go
/*********************************************************************************************************
*Created/Source by:sfibich
*ScriptName:
*Version:1.0.2013.08.01
*Tester:
*Tested on SQL Versions:2008R2
*Purpose:This query shows the statistics that are used by the query optimizer to
*generate a plan.  This plan is useful for deep database tuning.  Currently it only
*works with certian versions of SQL Server 2008 and 2008R2, see the ShowPlanXML
*portion of the query.  (Beta...but still a really useful script!)
*					 
*********************************************************************************************************/
DBCC FREEPROCCACHE
go
DBCC TRACEON (8666,-1);
go
SET ShowPlan_XML ON;
go
/*QP2*/
select * from sys.objects
go
SET ShowPlan_XML OFF;
go
 
select
qp.plan_handle 
,qp.text  
,x.c.value('(Field/@FieldValue)[1]', 'char(300)' ) as [db_name]
,x.c.value('(Field/@FieldValue)[2]', 'char(300)' ) as [schema_name]
,x.c.value('(Field/@FieldValue)[3]', 'char(300)' ) as [table_name]
,mti.c.value('(Field/@FieldValue)[1]', 'char(300)' ) as [stat_name]
,mti.c.value('(Field/@FieldValue)[2]', 'char(300)' ) as [column_name]
from
(
	select
	jtq.plan_handle 
	,jtq.text 
	,convert(xml,replace(replace(convert(varchar(max),qp.query_plan)
,'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.1817.0">','<ShowPlanXML>')
,'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.4330.0">','<ShowPlanXML>')
	) as [query_plan]
	from
	(
		select
		cp.plan_handle 
		,st.text
		from
		sys.dm_exec_cached_plans cp
		cross apply
		sys.dm_exec_sql_text(cp.plan_handle) st
		where
		cp.objtype ='AdHoc'	
		and
		left(st.text,7) = '/*QP2*/'
	)jtq
	cross apply
	sys.dm_exec_query_plan(jtq.plan_handle) qp
)qp
cross apply
qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/InternalInfo/EnvColl/Recompile') as x(c)
cross apply
x.c.nodes('ModTrackingInfo') as mti(c)
go
DBCC TRACEOFF (8666,-1)